﻿using AutoMapper;
using DFGK_WMS.Context;
using DFGK_WMS.IRepository;
using DFGK_WMS.Model.DTO;
using DFGK_WMS.Model.Models;
using NPOI.SS.Formula.Functions;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Management;
using System.Text;
using System.Threading.Tasks;

namespace DFGK_WMS.Repository
{
    public class VisuallargeScreenRepository : BaseRepository<TReceivingOrderDetail>, IVisuallargeScreenRepository
    {
        public VisuallargeScreenRepository(DFGK_DB context, IMapper mapper) : base(context, mapper)
        {
        }


        /// <summary>
        /// 待办已办任务统计
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="type"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<ToDoDoneDto> GetToDoDone(int pageIndex, int pageSize, int type, out int totalCount)
        {
            List<ToDoDoneDto> AllList = new List<ToDoDoneDto>();
            //待办
            if (type == 1)
            {
                var ReceivingList = Context.TReceivingOrderDetails.Where(x => x.Status != 3).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var Receiving in ReceivingList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = Receiving.OrderSn,
                        type = 1,
                        CreateTime = Receiving.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                var ShippingList = Context.TShippingOrderDetails.Where(x => x.Status != 4).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var Shipping in ShippingList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = Shipping.OrderSn,
                        type = 2,
                        CreateTime = Shipping.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                var TransferBinList = Context.TTransferBinOrders.Where(x => x.Status != 4).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var TransferBin in TransferBinList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = TransferBin.OrderSn,
                        type = 3,
                        CreateTime = TransferBin.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                var StocktakingList = Context.TStocktakingOrders.Where(x => x.Status != 4).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var Stocktaking in StocktakingList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = Stocktaking.OrderSn,
                        type = 4,
                        CreateTime = Stocktaking.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                totalCount = AllList.Count();
                return AllList.OrderByDescending(x => x.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

            }
            //已办
            else if (type == 2)
            {
                var ReceivingList = Context.TReceivingOrderDetails.Where(x => x.Status == 3).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var Receiving in ReceivingList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = Receiving.OrderSn,
                        type = 1,
                        CreateTime = Receiving.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                var ShippingList = Context.TShippingOrderDetails.Where(x => x.Status == 4).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var Shipping in ShippingList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = Shipping.OrderSn,
                        type = 2,
                        CreateTime = Shipping.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                var TransferBinList = Context.TTransferBinOrders.Where(x => x.Status == 4).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var TransferBin in TransferBinList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = TransferBin.OrderSn,
                        type = 3,
                        CreateTime = TransferBin.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                var StocktakingList = Context.TStocktakingOrders.Where(x => x.Status == 4).AsEnumerable().DistinctBy(x => x.OrderSn).ToList();
                foreach (var Stocktaking in StocktakingList)
                {
                    ToDoDoneDto toDoDoneDto = new ToDoDoneDto()
                    {
                        OrderSn = Stocktaking.OrderSn,
                        type = 4,
                        CreateTime = Stocktaking.CreateDate
                    };
                    AllList.Add(toDoDoneDto);
                }
                totalCount = AllList.Count();
                return AllList.OrderByDescending(x => x.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            else if (type == 5)
            {
                List<ToDoDoneDto> toDoDoneDto = new List<ToDoDoneDto>();
                var inventoryWarnList = Context.TInventoryWarn.ToList();
                foreach (var item in inventoryWarnList)
                {
                    ToDoDoneDto toDoDone = new ToDoDoneDto();
                    var warnList = Context.TWarningRules.Where(x => x.MaterialCode == item.MaterialCode);
                    if (warnList.Count() <= 0)
                    {
                        continue;
                    }
                    if (item.QTY >= warnList.First().WarningQtyMax)
                    {
                        toDoDone.materialCode = item.MaterialCode;
                        toDoDone.materialName = item.MaterialName;
                        toDoDone.qty = (decimal)item.QTY;
                        toDoDone.warnType = 1;
                        toDoDone.name = "物料" + item.MaterialName + "超过最大库存数量，当前数量:" + item.QTY + "，库存最大数量:" + warnList.FirstOrDefault().WarningQtyMax;
                        toDoDoneDto.Add(toDoDone);
                    }
                    else if (item.QTY <= warnList.First().WarningQtyMin)
                    {
                        toDoDone.materialCode = item.MaterialCode;
                        toDoDone.materialName = item.MaterialName;
                        toDoDone.qty = (decimal)item.QTY;
                        toDoDone.warnType = 2;
                        toDoDone.name = "物料" + item.MaterialName + "超过最小库存数量，当前数量:" + item.QTY + "，库存最小数量:" + warnList.FirstOrDefault().WarningQtyMin;
                        toDoDoneDto.Add(toDoDone);
                    }

                }

                totalCount = toDoDoneDto.Count();
                return toDoDoneDto.OrderByDescending(x => x.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            else
            {
                totalCount = 0;
                return null;
            }
        }


        /// <summary>
        /// 月出入移盘统计
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public EntryAndExitTransferStatisticsDto GetAllQty()
        {
            var startDate = DateTime.Today.AddDays(-30); // 从一周前开始计算
            var endDate = DateTime.Today; // 当天
            EntryAndExitTransferStatisticsDto dto = new EntryAndExitTransferStatisticsDto();
            //入库数量
            var ReceivingList = Context.TReceivingOrderDetails.AsQueryable().AsEnumerable().DistinctBy(x => x.OrderSn);
            var ReceivingListCount = ReceivingList.Where(x => x.CreateDate >= startDate && x.CreateDate <= endDate);
            dto.ReceivingQty = ReceivingListCount.Count();
            //出库数量
            var ShippingList = Context.TShippingOrderDetails.AsQueryable().AsEnumerable().DistinctBy(x => x.OrderSn);
            var ShippingListCount = ShippingList.Where(x => x.CreateDate >= startDate && x.CreateDate <= endDate);
            dto.ShippingQty = ShippingListCount.Count();
            //盘库数量
            var TStocktakingList = Context.TStocktakingOrders.AsQueryable().AsEnumerable().DistinctBy(x => x.OrderSn);
            var TStocktakingListCount = TStocktakingList.Where(x => x.CreateDate >= startDate && x.CreateDate <= endDate);
            dto.StocktakingQty = TStocktakingListCount.Count();
            //移库数量
            var TransferBinList = Context.TTransferBinOrders.AsQueryable().AsEnumerable().DistinctBy(x => x.OrderSn);
            var TransferBinListCount = TransferBinList.Where(x => x.CreateDate > startDate && x.CreateDate <= endDate);
            dto.TransferBinQty = TransferBinListCount.Count();
            return dto;
        }

        /// <summary>
        /// 获取库存总数
        /// </summary>
        /// <returns></returns>
        public InventoryQtyDto GetInventoryQty()
        {
            InventoryQtyDto dto = new InventoryQtyDto();
            var InventoryQtyNum = Context.TInventories.Sum(x => x.AvailableQty);
            dto.InventoryQty = (int)InventoryQtyNum;
            //var UseInventoryQtyNum = Context.TShippingLockCells.AsQueryable().Sum(x => x.OutQty);
            //dto.UseInventoryQty = (int)UseInventoryQtyNum;
            var inventoryList = Context.TInventories.ToList();
            foreach ( var item in inventoryList)
            {
                int qtyPrice = 0;
                if (item.Price.HasValue)
                {
                    qtyPrice = (int)(item.AvailableQty * item.Price.Value);
                }
                dto.UseInventoryQty = dto.UseInventoryQty += qtyPrice;
            }
            var GoodProductQtyNum = Context.TInventories.Where(x => x.QualityState == 0 || x.QualityState == 1).GroupBy(x => x.MaterialCategoryId).Select(group => group.First()).ToList();
            dto.GoodProductQty = GoodProductQtyNum.Count();
            var NotGoodProductNum = Context.TInventories.GroupBy(x => x.MaterialName).Select(group => group.First()).ToList();
            dto.NotGoodProductQty = NotGoodProductNum.Count() ;
            return dto;
        }

        /// <summary>
        /// 根据质量等级查询
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<QualityStateDto> GetQtyByQualityState()
        {
            List<QualityStateDto> qualityStateDtos = new List<QualityStateDto>();

            var inventoryQty = Context.TInventories
                .GroupBy(x => x.QualityState)
                .Select(group => new QualityStateDto
                {
                    name = group.Key,
                    value = (int)group.Sum(x => x.AvailableQty)
                })
                .ToList();

            qualityStateDtos.AddRange(inventoryQty);

            return qualityStateDtos;
        }

        /// <summary>
        /// 出入库数量统计
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public List<AppointDateQtyDto> GetQtyByWork(string StartTime, string EndTime)
        {
            DateTime startDateTime = DateTime.Parse(StartTime);
            DateTime endDateTime = DateTime.Parse(EndTime).AddDays(1); // 结束时间需要加一天，以包含该天的数据

            List<AppointDateQtyDto> appointDateQtyDtos = new List<AppointDateQtyDto>();

            var ReceivingList = Context.TReceivingOrderDetails
                    .Where(x => x.CreateDate >= startDateTime && x.CreateDate <= endDateTime)
                    .GroupBy(x => x.CreateDate.Date)
                    .Select(data => new AppointDateQtyDto
                    {
                        DateTime = data.Key,
                        ReceivingQty = (int)data.Sum(x => x.Qty),
                        ShippingQty = 0
                    })
                    .ToList();

            //appointDateQtyDtos.AddRange(ReceivingList);
            var ShippingList = Context.TShippingOrderDetails.Where(x => x.CreateDate >= startDateTime && x.CreateDate <= endDateTime)
                .AsEnumerable()
                .GroupBy(x => x.CreateDate.Date)
                .Select(data => new AppointDateQtyDto
                {
                    DateTime = data.Key,
                    ShippingQty = (int)Context.TShippingLockCells
                         .Where(x => x.OrderDetailId == data.First().Id)
                         .Sum(x => x.OutQty),
                    ReceivingQty = 0
                })
                .ToList();

            //appointDateQtyDtos.AddRange(shippingListF);

            var groupedResult = ReceivingList.Concat(ShippingList) // 合并两个列表
        .GroupBy(x => x.DateTime)
        .Select(data => new AppointDateQtyDto
        {
            DateTime = data.Key,
            ReceivingQty = data.Sum(x => x.ReceivingQty), // 计算入库数量总和
            ShippingQty = data.Sum(x => x.ShippingQty) // 计算出库数量总和
        })
        .ToList();

            return groupedResult;
        }

        ///// <summary>
        ///// 按装备大类统计
        ///// </summary>
        ///// <returns></returns>
        //public List<MaterialCategoryGroup> GetMaterialCategoryQty()
        //{
        //    List<MaterialCategoryGroup> materialCategoryGroups = new List<MaterialCategoryGroup>();

        //    var inventoryQty = Context.TInventories
        //        .GroupBy(x => x.MaterialCategoryId)
        //        .Select(group => new MaterialCategoryGroup
        //        {
        //            MaterialCategoryId = group.Key,
        //            Qty = (int)group.Sum(x => x.AvailableQty),
        //            MaterialCategoryName = Context.TMaterialCategories.First(x => x.Id == group.Key).Name,
        //        })
        //          .Where(group => group.MaterialCategoryName != null) // 筛选掉空值
        //        .ToList();

        //    materialCategoryGroups.AddRange(inventoryQty);

        //    return materialCategoryGroups;
        //}


        /// <summary>
        /// 按装备大类统计
        /// </summary>
        /// <returns></returns>
        public List<MaterialCategoryGroup> GetMaterialCategoryQty()
        {
            var materialCategoryTypeList = Context.TMaterialCategories
                    .GroupBy(x => x.EquipmentType)
                    .Select(group => group.First())
                    .ToList();
            List<MaterialCategoryGroup> materialCategoryGroups = new List<MaterialCategoryGroup>();
            foreach (var item in materialCategoryTypeList)
            {
                MaterialCategoryGroup materialCategoryGroup = new MaterialCategoryGroup();
                materialCategoryGroup.MaterialCategoryType = item.EquipmentType;
                decimal Qty = 0;
                var materialCategoryList = Context.TMaterialCategories.Where(x => x.EquipmentType == item.EquipmentType).ToList();
                foreach (var order in materialCategoryList)
                {
                    // Assuming AvailableQty is of type int in MaterialCategoryGroupChild
                    Qty = (int)Context.TInventories
                        .Where(x => x.MaterialCategoryId == order.Id)
                        .Sum(x => x.AvailableQty);
                    materialCategoryGroup.Qty += Qty;

                }
                materialCategoryGroups.Add(materialCategoryGroup);

            }
            return materialCategoryGroups;

        }

        /// <summary>
        /// 按装备大类统计
        /// </summary>
        /// <returns></returns>
        public List<MaterialGroups> GetMaterialGroupsQty()
        {
            List<MaterialGroups> materialGroups = new List<MaterialGroups>();

            var inventoryQty = Context.TInventories
                .GroupBy(x => x.MaterialGroupId)
                .Select(group => new MaterialGroups
                {
                    MaterialGroupsCode = group.Key,
                    Qty = (int)group.Sum(x => x.AvailableQty),
                    MaterialGroupName = Context.TMaterialGroups.First(x => x.Code == group.Key).Name,
                })
                .ToList();

            materialGroups.AddRange(inventoryQty);

            return materialGroups;
        }

        public string GetUSBPath()
        {

            // 定义WMI查询语句
            string query = "SELECT * FROM Win32_USBControllerDevice";

            string usbDevicePath = "";
            // 创建ManagementObjectSearcher对象并执行查询
            using (ManagementObjectSearcher searcher = new ManagementObjectSearcher(query))
            {
                // 获取查询结果
                foreach (ManagementObject obj in searcher.Get())
                {
                    // 获取USB设备的路径
                    string path = obj["Dependent"].ToString();
                    Console.WriteLine("USB设备路径：" + path);
                    usbDevicePath = path;
                }
            }

            // 构造WMI查询语句
            string querys = "ASSOCIATORS OF {" + usbDevicePath + "} WHERE ResultClass=CIM_DataFile";

            // 创建ManagementObjectSearcher对象并执行查询
            using (ManagementObjectSearcher searcher = new ManagementObjectSearcher(querys))
            {
                // 获取查询结果
                foreach (ManagementObject obj in searcher.Get())
                {
                    // 输出文件信息，可以根据需要进行处理
                    Console.WriteLine("文件路径：" + obj["Name"]);
                }
            }
            return usbDevicePath;
        }

        /// <summary>
        /// 大屏调拨/自购入库查询
        /// </summary>
        /// <returns></returns>
        public List<MaterialTypeCountDto> GetMaterialTypeCount()
        {
            var list = Context.TReceivingOrderDetails.ToList();
            List<MaterialTypeCountDto> materialTypeCountDtos = new List<MaterialTypeCountDto>();

            var allocationList = list.Where(x => x.BillType == 1073);
            int Other1Qty = 0;
            foreach (var item in allocationList)
            {
                Other1Qty += (int)item.ReceivedQty;
            }
            MaterialTypeCountDto materialTypeCount = new MaterialTypeCountDto();
            materialTypeCount.materialType = 1;
            materialTypeCount.count = Other1Qty;
            materialTypeCountDtos.Add(materialTypeCount);

            var otherList = list.Where(x => x.BillType != 1073);
            int Other2Qty = 0;
            foreach (var item in otherList)
            {
                Other2Qty += (int)item.ReceivedQty;
            }
            MaterialTypeCountDto materialType = new MaterialTypeCountDto();
            materialType.materialType = 2;
            materialType.count = Other2Qty;
            materialTypeCountDtos.Add(materialType);

            return materialTypeCountDtos;
        }


        /// <summary>
        /// 入库
        /// </summary>
        /// <returns></returns>
        public List<EquipmentReceivingDto> GetEquipmentReceiving()
        {
            List<EquipmentReceivingDto> equipmentReceivingDtos = new List<EquipmentReceivingDto>();

            #region
            /*
            var receivingList = Context.TReceivingOrderDetails.ToList();
            List<string> strings = new List<string>();
            foreach ( var receiving in receivingList)
            {
                if(receiving.Contact == null || receiving.Contact == "")
                {
                    continue;
                }
                foreach (var test in strings)
                {
                    if (receiving.Contact.Equals(test))
                    {
                        continue;
                    }
                }

                var suppliersList = Context.TCustomers.Where(x => x.RoleName.Equals(receiving.Contact));
                if(suppliersList.Count() > 0)
                {
                    EquipmentReceivingDto equipmentReceivingDto = new EquipmentReceivingDto();
                    equipmentReceivingDto.Name = receiving.Contact;
                    decimal Qty = 0;
                    foreach (var item in suppliersList)
                    {
                        var orderList = receivingList.Where(x => item.Contact.Equals(x.Contact));
                        Qty = (decimal)orderList.Sum(x => x.ReceivedQty);
                    }
                    equipmentReceivingDto.Qty = Qty;

                }
                strings.Add(receiving.Contact);
            }
            */
            #endregion

            var roleList = Context.TUserRoles.Where(x => x.RoleId == 2010).ToList();
            List<TUser> userLists = new List<TUser>();
            foreach (var item in roleList)
            {
                var userList = Context.TUsers.Where(x => x.Id == item.UserId).ToList();
                userLists.Add(userList.First());
            }
            foreach (var item in userLists)
            {
                var suppliersList = Context.TSuppliers.Where(x => x.RoleName.Equals(item.RealName)).ToList();
                foreach (var supplier in suppliersList)
                {
                    EquipmentReceivingDto equipmentReceivingDto = new EquipmentReceivingDto();
                    equipmentReceivingDto.Name = supplier.Name;
                    var receivingList = Context.TReceivingOrderDetails
                                             .Where(x => x.SupplierId == supplier.Id)
                                             .ToList();
                    equipmentReceivingDto.Qty = (decimal)receivingList.Sum(x => x.Qty);

                    equipmentReceivingDtos.Add(equipmentReceivingDto);
                }
            }

            return equipmentReceivingDtos;
        }


        /// <summary>
        ///出库
        /// </summary>
        /// <returns></returns>
        public List<EquipmentShippingDto> GetEquipmentShipping()
        {
            List<EquipmentShippingDto> equipmentShippingDtos = new List<EquipmentShippingDto>();

            var shippingList = Context.TShippingOrderDetails.ToList();

            foreach (var shipping in shippingList)
            {
                if (shipping.SupplierId == 0 || shipping.SupplierId == null)
                {
                    continue;
                }

                // 获取供应商名称
                string supplierName = Context.TSuppliers
                                              .Where(s => s.Id == shipping.SupplierId)
                                              .Select(s => s.Name)
                                              .FirstOrDefault();

                // 创建一个新的 EquipmentShippingDto 对象
                EquipmentShippingDto shippingDto = new EquipmentShippingDto
                {
                    // 将 SupplierName 设置为供应商名称
                    Name = supplierName
                };

                // 在 TShippingLockCells 中查找对应 OrderDetailId 的记录，并进行数量求和
                decimal? qty = Context.TShippingLockCells
                                     .Where(x => x.OrderDetailId == shipping.Id)
                                     .Sum(x => (decimal?)x.OutQty);

                // 将数量设置到 EquipmentShippingDto 对象中
                shippingDto.Qty = qty ?? 0;

                // 添加到列表中
                equipmentShippingDtos.Add(shippingDto);
            }

            // 根据供应商名称对列表进行分组，并将每个分组中的数量相加
            var result = equipmentShippingDtos.GroupBy(x => x.Name)
                                               .Select(g => new EquipmentShippingDto
                                               {
                                                   Name = g.Key,
                                                   Qty = g.Sum(x => x.Qty)
                                               })
                                               .ToList();

            return result;
        }
    }
}
